package com.jdbc;

/**
 * @author isam2016
 * @create 2021-08-20 2:45 PM
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoddessDao {
    //增加
    public void addGoddess(Goddess g) throws SQLException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        try {
            //获取连接
            conn = DbUtil.getConnection();
            //sql
            String sql = "INSERT INTO user(user_name, sex, age, birthday, email, mobile,"+
                    "create_user, update_user, isdel)"
                    +"values("+"?,?,?,?,?,?,?,?,?)";
            //预编译(完全避免SQL注入)
            ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行
            //传参
            ptmt.setString(1, g.getUser_name());
            ptmt.setInt(2, g.getSex());
            ptmt.setInt(3, g.getAge());
            ptmt.setDate(4, new Date(g.getBirthday().getTime()));
            ptmt.setString(5, g.getEmail());
            ptmt.setString(6, g.getMobile());
            ptmt.setString(7, g.getCreate_user());
            ptmt.setString(8, g.getUpdate_user());
            ptmt.setInt(9, g.getIsDel());

            //执行
            ptmt.execute();
        }finally {
            ptmt.close();
            conn.close();
        }

    }

    public void updateGoddess(Goddess g) throws SQLException{
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "UPDATE user" +
                " set user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?,"+
                " update_user=?, update_date=CURRENT_DATE(), isdel=? "+
                " where id=?";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, g.getUser_name());
        ptmt.setInt(2, g.getSex());
        ptmt.setInt(3, g.getAge());
        ptmt.setDate(4, new Date(g.getBirthday().getTime()));
        ptmt.setString(5, g.getEmail());
        ptmt.setString(6, g.getMobile());
        ptmt.setString(7, g.getUpdate_user());
        ptmt.setInt(8, g.getIsDel());
        ptmt.setInt(9, g.getId());

        //执行
        ptmt.execute();
    }

    public void delGoddess(int id) throws SQLException{
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "delete from user where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);

        //传参
        ptmt.setInt(1, id);

        //执行
        ptmt.execute();
    }

    public List<Goddess> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM user");

        List<Goddess> gs = new ArrayList<Goddess>();
        Goddess g = null;
        while(rs.next()){
            g = new Goddess();
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));

            gs.add(g);
        }
        return gs;
    }

    public Goddess get(int id) throws Exception{
        Goddess g = null;
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "select * from  user where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setInt(1, id);
        //执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));
            g.setSex(rs.getInt("sex"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setCreate_user(rs.getString("create_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setIsDel(rs.getInt("isdel"));
        }
        return g;
    }

    public static void main(String[] args) throws Exception{
        GoddessDao goddessDao = new GoddessDao();
        Goddess g = new Goddess();
        g.setId(2);
        g.setUser_name("ism2016");
        g.setAge(12);
        g.setSex(0);
        g.setBirthday(new java.util.Date());
        g.setEmail("1228901986@qq.com");
        g.setMobile("13356689788");
        g.setCreate_date(new java.util.Date());
        g.setCreate_user("isam2016");
        g.setUpdate_user("idsam2016");
        g.setIsDel(1);
        goddessDao.addGoddess(g);
    }
}